#include <stdio.h>
#include <errno.h>
#include <string.h>
#include <stdlib.h>
#include "sqlite3.h"

#include "database.h"
#include "logger.h"

#define LEN		128
#define TABLE_NAME	"TEMPER"


//创建或打开数据库、建表
int database_init(sqlite3 **db, char *db_name)
{
	int			rv = -1;
	char		*sql;
	char		*zErrMsg = 0;

	//打开数据库
	rv = sqlite3_open(db_name, db);
	if(rv < 0)
	{
		log_error("Open database failure: %s\n", strerror(errno));
		return -1;
	}

	//建表
	sql = "CREATE TABLE IF NOT EXISTS TEMPER(     "	\
	"ID INTEGER   PRIMARY KEY	AUTOINCREMENT,"	\
	"SN 	      CHAR(10) 		NOT NULL,"	\
	"DATATIME     CHAR(50)		NOT NULL,"	\
	"TEMPERATURE  CHAR(10)  	NOT NULL);"	;

	rv = sqlite3_exec(*db, sql, 0, 0, &zErrMsg);
	if(rv != SQLITE_OK)
	{
		log_debug("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_info("Create database and table OK!\n");
	}

	return 0;
}

//插入数据
int database_insert(sqlite3 *db, char *sample)
{
	int			rv = -1;
	char		*ID, *temp, *time;
	char 		buf[128];
	char		*zErrMsg = 0;
	char		**Result;

	ID = strtok(sample, "/");
	time = strtok(NULL, "/");
	temp = strtok(NULL, "/");

	memset(buf, 0, sizeof(buf));
	snprintf(buf, 128, "INSERT INTO TEMPER VALUES(NULL, '%s', '%s', '%s');", ID, time, temp);
	rv = sqlite3_exec(db, buf, 0, 0, &zErrMsg);
	if(rv != SQLITE_OK)
	{
		log_debug("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	else
	{
		log_info("Insert data to database OK!\n");
	}

	return 0;
}

//取出第一条记录
int database_pop_first(sqlite3 *db, char *data)
{
	int			rv = -1;
	int			nrow = 0;
	char		*zErrMsg = 0;
	char		**Result = NULL;
	char		buf[128];

	memset(buf, 0, sizeof(buf));
	snprintf(buf, LEN, "SELECT * FROM %s LIMIT 1", TABLE_NAME);
	rv = sqlite3_get_table(db, buf, &Result, &nrow, NULL, &zErrMsg);
	if(rv != SQLITE_OK)
	{
		log_debug("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}

	if(0 == nrow)
	{
		sqlite3_free_table(Result);
		return -2;
	}

	else
	{
		memset(data, 0, sizeof(data));
		snprintf(data, LEN, "%s/%s/%s", Result[5], Result[6], Result[7]);
		sqlite3_free_table(Result);
	}

	return 0;
}

//删除第一条记录
int database_delete_first(sqlite3 *db)
{
	int			rv = -1;
	char		buf[128];
	char		*zErrMsg = 0;

	memset(buf, 0, sizeof(buf));
	snprintf(buf, LEN, "DELETE FROM %s WHERE ID IN (SELECT ID FROM %s  LIMIT 1)", TABLE_NAME, TABLE_NAME);

	rv = sqlite3_exec(db, buf, 0, 0, &zErrMsg);
	if(rv != SQLITE_OK)
	{
		log_debug("SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -1;
	}
	
	return 0;
}

//关闭数据库
void database_term(sqlite3 *db)
{

	sqlite3_close(db);

}

//获取第一条数据并填充结构体
int database_get_first(sqlite3 *db, sample_data_t *s_data)
{
    int         rv = -1;
    char        *zErrMsg = 0;
    char        **Result = NULL;
    char        buf[128];

    memset(buf, 0, sizeof(buf));
    snprintf(buf, LEN, "SELECT * FROM %s LIMIT 1", TABLE_NAME);
    rv = sqlite3_get_table(db, buf, &Result, NULL, NULL, &zErrMsg);
    if(rv != SQLITE_OK)
    {
        log_debug("SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -1;
    }
    else
    {
        memset(s_data, 0, sizeof(s_data));
		strncpy(s_data->ID, Result[5], sizeof(s_data->ID));
		strncpy(s_data->datatime, Result[6], sizeof(s_data->datatime));
		s_data->temp = atof(Result[7]);
    }

    sqlite3_free_table(Result);

	return 0;
}
